package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import modelo.Aluno;
import modelo.Aula;
import modelo.Turma;
import conexao.ConnectionManager;

public class AlunoDaoImp implements AlunoDAO{
	private final String INSERT = "insert into aluno (nome, telefone, endereco, cpf, email, matricula, idTurma)values(?,?,?,?,?,?,?)";
	private final String UPDATE = "update aluno set nome = ?, telefone = ?, endereco = ?, cpf = ?, email = ?, matricula =? idTurma = ? where idAluno = ?";
	private final String DELETE = "delete from aluno where idAluno = ?";
	private final String ALL = "select * from aluno";
	private final String ID = "select * from aluno where idAluno = ?";
	private final String CPF = "select * from aluno where cpf = ?";
	private final String NOME = "select * from aluno where nome like ?";
	private final String BUSCAR = "SELECT a.nome, a.telefone, a.endereco, a.cpf," +
				" a.email, a.matricula, a.idTurma FROM aluno AS a JOIN turma AS t ON a.idTurma = t.idTurma WHERE t.nome LIKE ?";
	
	private static Connection connection;
	
	public AlunoDaoImp() {
		openConnection();
	}
	@Override
	public Aluno salvar(Aluno e) {
		try{
		PreparedStatement ps = null;
		ps = ConnectionManager.getInstance().getConnection().prepareStatement(INSERT);
		ps.setObject(1, e.getNome());
		ps.setObject(2, e.getTelefone());
		ps.setObject(3, e.getEndereco());
		ps.setObject(4, e.getCpf());
		ps.setObject(5, e.getEmail());
		ps.setObject(6, e.getMatricula());
		ps.setObject(7, e.getTurma().getIdTurma());
		ps.executeUpdate();
		}catch(SQLException ex){
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null,ex);
			
		}finally{
			this.closeConnection();
		}
		return e;
	}
	@Override
	public void alterar(Aluno e) {
		try{
			PreparedStatement ps = null;
			ps = ConnectionManager.getInstance().getConnection().prepareStatement(UPDATE);
			ps.setObject(1, e.getNome());
			ps.setObject(2, e.getTelefone());
			ps.setObject(3, e.getEndereco());
			ps.setObject(4, e.getCpf());
			ps.setObject(5, e.getEmail());
			ps.setObject(6, e.getMatricula());
			ps.setObject(7, e.getTurma().getIdTurma());
			ps.executeUpdate();
		}catch(SQLException ex){
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,null,ex);
		}finally{
			this.closeConnection();
		}
	}
	@Override
	public void deletar(Aluno e) {
		try{
			PreparedStatement ps = null;
			ps = connection.prepareStatement(DELETE);
			ps.setObject(1, e.getIdAluno());
			ps.executeUpdate();
		}catch(SQLException ex){
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,null,ex);
		}finally{
			this.closeConnection();
		}
	}
	@Override
	public List findAll() {
		List<Aluno> alunos = new ArrayList<Aluno>();
		
		try{
			PreparedStatement ps = null;
			ResultSet rs = null;
			ps = connection.prepareStatement(ALL);
			rs = ps.executeQuery();
			while (rs.next()) {
				Aluno aluno = new Aluno();
				aluno.setIdAluno(rs.getInt("idAluno"));
				aluno.setNome(rs.getString("nome"));
				aluno.setTelefone(rs.getString("telefone"));
				aluno.setEndereco(rs.getString("endereco"));
				aluno.setCpf(rs.getString("cpf"));
				aluno.setEmail(rs.getString("email"));
				aluno.setMatricula(rs.getInt("matricula"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idTurma"));
				aluno.setTurma(turma);
				alunos.add(aluno);
				
			}
			}catch(SQLException ex){
				Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE,null,ex);
				
			} finally {
				this.closeConnection();
			}
			return alunos;
	}

	public boolean isConnectionClose() {
		try {
			return connection.isClosed();
		} catch (SQLException ex) {
			return false;
		}
		
	}

	@Override
	public void openConnection() {
		if ( connection == null) {
			connection = ConnectionManager.getInstance().getConnection();
		}
		
		if ( isConnectionClose() ) {
			connection = ConnectionManager.getInstance().getConnection();
		}	
	}

	@Override
	public void closeConnection() {
		if ( connection != null && !this.isConnectionClose()) {
			ConnectionManager.getInstance().closeConnection(connection);
		}
	}
	@Override
	public Aluno buscarPorID(int idAluno) {
		List<Aluno> alunos = new ArrayList<Aluno>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(ID);
			ps.setObject(1, idAluno);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aluno aluno = new Aluno();
				aluno.setIdAluno(rs.getInt("idAluno"));
				aluno.setNome(rs.getString("nome"));
				aluno.setTelefone(rs.getString("telefone"));
				aluno.setEndereco(rs.getString("endereco"));
				aluno.setCpf(rs.getString("cpf"));
				aluno.setEmail(rs.getString("email"));
				aluno.setMatricula(rs.getInt("matricula"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idTurma"));
				aluno.setTurma(turma);
				alunos.add(aluno);
			}

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}
		if (alunos.isEmpty()) {
			return null;
		} else {
			return alunos.get(0);
		}
	}
	@Override
	public List<Aluno> buscarPorNome(String nome) {
		List<Aluno> alunos = new ArrayList<Aluno>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(NOME);
			ps.setObject(1, nome+"%");
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aluno aluno = new Aluno();
				aluno.setIdAluno(rs.getInt("idAluno"));
				aluno.setNome(rs.getString("nome"));
				aluno.setTelefone(rs.getString("telefone"));
				aluno.setEndereco(rs.getString("endereco"));
				aluno.setCpf(rs.getString("cpf"));
				aluno.setEmail(rs.getString("email"));
				aluno.setMatricula(rs.getInt("matricula"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idTurma"));
				aluno.setTurma(turma);
				alunos.add(aluno);
				
			}

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}if (alunos.isEmpty()) {
			return null;
		} else {
			return alunos;
		}
	}
	@Override
	public Aluno buscarPorCPF(String cpf) {
		List<Aluno> alunos = new ArrayList<Aluno>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(CPF);
			ps.setObject(1, cpf);
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aluno aluno = new Aluno();
				aluno.setIdAluno(rs.getInt("idAluno"));
				aluno.setNome(rs.getString("nome"));
				aluno.setTelefone(rs.getString("telefone"));
				aluno.setEndereco(rs.getString("endereco"));
				aluno.setCpf(rs.getString("cpf"));
				aluno.setEmail(rs.getString("email"));
				aluno.setMatricula(rs.getInt("matricula"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idTurma"));
				aluno.setTurma(turma);
				alunos.add(aluno);
				
			}

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}if (alunos.isEmpty()) {
			return null;
		} else {
			return alunos.get(0);
		}
	}
	@Override
	public List<Aluno> buscarPorTurma(String nome) {
		List<Aluno> alunos = new ArrayList<Aluno>();

		try {
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			ps = connection.prepareStatement(BUSCAR);
			ps.setObject(1, nome+"%");
			
			rs = ps.executeQuery();
			
			while (rs.next()) {
				Aluno aluno = new Aluno();
				//aluno.setIdAluno(rs.getInt("idAluno"));
				aluno.setNome(rs.getString("nome"));
				aluno.setTelefone(rs.getString("telefone"));
				aluno.setEndereco(rs.getString("endereco"));
				aluno.setCpf(rs.getString("cpf"));
				aluno.setEmail(rs.getString("email"));
				aluno.setMatricula(rs.getInt("matricula"));
				Turma turma = DAOFactory.getTurmaDAO().buscarPorID(rs.getInt("idTurma"));
				aluno.setTurma(turma);
				alunos.add(aluno);
				
			}

		} catch (SQLException ex) {
			Logger.getLogger(AlunoDaoImp.class.getName()).log(Level.SEVERE, null, ex);
		} finally {
			this.closeConnection();
		}if (alunos.isEmpty()) {
			return null;
		} else {
			return alunos;
		}
	}
	
	//buscarPorTurma(String nomeTurma) : List<Aluno>
	// SELECT a.nome, a.telefone, a.endereco, a.cpf, a.email, a.matricula FROM aluno AS a JOIN turma AS t ON a.idTurma = t.idTurma WHERE t.nome LIKE '%?%'
	
	

}
